﻿using System;
using System.Data;
using System.Collections.Generic;
using System.Text;
using System.Reflection;

namespace WsProtocol.IEC62056MDL
{
    public class clsUpdate
    {
        /// <summary>
        /// 获取升级语句
        /// </summary>
        /// <returns></returns>
        public static List<string> GetUpdateSQLList()
        {
            List<string> lststr = new List<string>();
            string StrSQL = "";
            StringBuilder strB = new StringBuilder();

            #region 以后的升级语句都写在这里即可，不需改任何其他代码和版本，每次用region区分注释

            #region 给方案明细表修改主键为ID，SchID，解决导入相同方案又不复盖原方案时ID重复
            lststr.Add("Begin Transaction");
            lststr.Add("Create  TABLE MAIN.[Temp_SchemaItem]([ID] varchar(50),[PID] varchar(50),[ObAttrMthID] varchar(50),[SchID] varchar(50) NOT NULL," +
                "[DataValue] varchar(50),[SNO] int,[RealValue] varchar(50), Primary Key(ID,SchID))");
            lststr.Add("Insert Into MAIN.[Temp_SchemaItem] ([ID],[PID],[ObAttrMthID],[SchID],[DataValue],[SNO],[RealValue]) " +
                "Select [ID],[PID],[ObAttrMthID],[SchID],[DataValue],[SNO],[RealValue] From MAIN.[T_SchemaItem]");
            lststr.Add("Drop Table MAIN.[T_SchemaItem]");
            lststr.Add("Alter Table MAIN.[Temp_SchemaItem] Rename To [T_SchemaItem]");
            lststr.Add("Commit Transaction");
            #endregion

            #region 修改方案项参数表里的数据类型  2019-3-1
            #endregion
            lststr.Add("update T_SchemaItemPar set datatype='bit_string' where datatype='bstring'");
            lststr.Add("update T_SchemaItemPar set datatype='octet_string' where datatype='octString'");
            lststr.Add("update T_SchemaItemPar set datatype='visible_string' where datatype='visiblestring'");
            lststr.Add("update T_SchemaItemPar set datatype='boolean' where datatype='Bool'");
            lststr.Add("update T_SchemaItemPar set datatype='double_long' where datatype='doublelong'");
            lststr.Add("update T_SchemaItemPar set datatype='double_long_unsigned' where datatype='doublelongunsigned'");
            lststr.Add("update T_SchemaItemPar set datatype='bcd' where datatype='BCD'");
            lststr.Add("update T_SchemaItemPar set datatype='Long' where datatype='ilong'");
            lststr.Add("update T_SchemaItemPar set datatype='long_unsigned' where datatype='longunsigned'");
            lststr.Add("update T_SchemaItemPar set datatype='date_time' where datatype='datetime'");
            lststr.Add("update T_SchemaItemPar set datatype='utf8_string' where datatype='UTF8_string'");
            lststr.Add("update T_SchemaItemPar set datatype='compact_array' where datatype='Comarray'");
            lststr.Add("update T_SchemaItemPar set datatypeDIY='bit_string' where datatypeDIY='bstring'");
            lststr.Add("update T_SchemaItemPar set datatypeDIY='date_time' where datatypeDIY='datetime'");
            lststr.Add("update T_SchemaItemPar set datatypeDIY='octet_string' where datatypeDIY='octString'");
            lststr.Add("update T_SchemaItemPar set datatypeDIY='visible_string' where datatypeDIY='visiblestring'");
            lststr.Add("update T_SchemaItemPar set datatypeDIY='boolean' where datatypeDIY='Bool'");
            lststr.Add("update T_SchemaItemPar set datatypeDIY='double_long' where datatypeDIY='doublelong'");
            lststr.Add("update T_SchemaItemPar set datatypeDIY='double_long_unsigned' where datatypeDIY='doublelongunsigned'");
            lststr.Add("update T_SchemaItemPar set datatypeDIY='bcd' where datatypeDIY='BCD'");
            lststr.Add("update T_SchemaItemPar set datatypeDIY='Long' where datatypeDIY='ilong'");
            lststr.Add("update T_SchemaItemPar set datatypeDIY='long_unsigned' where datatypeDIY='longunsigned'");
            lststr.Add("update T_SchemaItemPar set datatypeDIY='utf8_string' where datatypeDIY='UTF8_string'");
            lststr.Add("update T_SchemaItemPar set datatypeDIY='compact_array' where datatypeDIY='Comarray'");
            #endregion

            #region 方案项参数表增加RealValue字段，用于保存参数帧内值  2019-4-15
            lststr.Add("alter table T_SchemaItemPar add RealValue varchar(50)");
            #endregion

            #region 掌机接口相关表  2019-10-22
            lststr.Add("CREATE TABLE [T_HHULog] ("
                      + "[ExecutionTime] CHAR(19) NOT NULL," 
                      + "[ProGramName] CHAR(50)," 
                      + "[MeterNumber] CHAR(16)," 
                      + "[MeterAddress] CHAR(2)," 
                      + "[OBISName] CHAR(30)," 
                      + "[OBISValue] CHAR(1000)," 
                      + "[RWFlag] CHAR(50)," 
                      + "[SetData] CHAR(1000)," 
                      + "[ReadData] CHAR(1000)," 
                      + "[UserName] CHAR(50)," 
                      + "[Result] CHAR(50),"
                      + "[Seq] INTEGER(10000))");

            lststr.Add("CREATE TABLE [T_HHUProMeterNumber] ("
                      + "[MeterNumber] CHAR(16) NOT NULL," 
                      + "[ExecutionTime] CHAR(19)," 
                      + "[ProGramName] CHAR(50)," 
                      + "[Seq] INTEGER)");

            lststr.Add("CREATE TABLE [T_HHUUser] ("
                      + "[ID] varchar(20) NOT NULL,"
                      + "[HuuName] CHAR(50),"
                      + "[HuuPsw] CHAR(50), Primary Key(ID))");
            #endregion

            #region 新增掌机FTP信息表  shaunwang 2019-10-30
            lststr.Add("CREATE TABLE [T_HHUFtp] ("
                      + "[IP] varchar(20) NOT NULL,"
                      + "[Port] INTEGER,"
                      + "[UserName] varchar(50),"
                      + "[Password] varchar(50))");
            #endregion
            return lststr;
        }

    }
}
